/**
 * 查看NPC基础数据统计信息
 */

const mysql = require('mysql2/promise');

const dbConf = {
  host: '43.143.253.188',
  port: 3306,
  user: 'nextjs',
  password: 'yEAiRE2jcxTyRBy5',
  database: 'nextjs',
  charset: 'utf8mb4'
};

async function showNpcStats () {
  const conn = await mysql.createConnection(dbConf);
  try {
    console.log('📊 NPC基础数据统计信息:');

    // 总体统计
    const [clusterTotal] = await conn.execute('SELECT COUNT(*) as total FROM npc_clusters');
    const [detailTotal] = await conn.execute('SELECT COUNT(*) as total FROM npc_details');
    console.log(`\n总计: ${clusterTotal[0].total} 个NPC集群，${detailTotal[0].total} 个NPC详情`);

    // 按类型统计
    const [typeStats] = await conn.execute(`
            SELECT 
                type_name,
                type_id,
                COUNT(*) as count,
                AVG(area_level) as avg_level,
                MIN(area_level) as min_level,
                MAX(area_level) as max_level,
                AVG(attack_power) as avg_attack
            FROM npc_clusters 
            GROUP BY type_id, type_name
            ORDER BY type_id
        `);

    console.log('\n按NPC类型统计:');
    console.table(typeStats.map(row => ({
      '类型名称': row.type_name,
      '类型ID': row.type_id,
      '数量': row.count,
      '平均等级': Math.round(row.avg_level || 0),
      '等级范围': `${row.min_level || 0}-${row.max_level || 0}`,
      '平均攻击力': Math.round(row.avg_attack || 0)
    })));

    // 按等级范围统计
    const [levelStats] = await conn.execute(`
            SELECT 
                CASE 
                    WHEN area_level <= 10 THEN '1-10级'
                    WHEN area_level <= 30 THEN '11-30级'
                    WHEN area_level <= 60 THEN '31-60级'
                    WHEN area_level <= 100 THEN '61-100级'
                    ELSE '100级以上'
                END as level_range,
                COUNT(*) as count,
                AVG(attack_power) as avg_attack
            FROM npc_clusters 
            WHERE area_level IS NOT NULL
            GROUP BY 
                CASE 
                    WHEN area_level <= 10 THEN '1-10级'
                    WHEN area_level <= 30 THEN '11-30级'
                    WHEN area_level <= 60 THEN '31-60级'
                    WHEN area_level <= 100 THEN '61-100级'
                    ELSE '100级以上'
                END
            ORDER BY MIN(area_level)
        `);

    console.log('\n按等级范围统计:');
    console.table(levelStats.map(row => ({
      '等级范围': row.level_range,
      'NPC数量': row.count,
      '平均攻击力': Math.round(row.avg_attack || 0)
    })));

    // 高等级地区详情
    const [highLevelAreas] = await conn.execute(`
            SELECT 
                area_name,
                area_level,
                COUNT(*) as npc_count,
                AVG(attack_power) as avg_attack,
                GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') as npc_names
            FROM npc_clusters 
            WHERE area_level >= 100
            GROUP BY area_name, area_level
            ORDER BY area_level DESC
        `);

    console.log('\n🏔️ 高等级地区详情 (100级以上):');
    console.table(highLevelAreas.map(row => ({
      '地区名称': row.area_name,
      '地区等级': row.area_level,
      'NPC数量': row.npc_count,
      '平均攻击力': Math.round(row.avg_attack || 0),
      'NPC名称': row.npc_names.length > 50 ? row.npc_names.substring(0, 50) + '...' : row.npc_names
    })));

    // 新手地区详情
    const [newbieAreas] = await conn.execute(`
            SELECT 
                area_name,
                area_level,
                COUNT(*) as npc_count,
                AVG(attack_power) as avg_attack,
                GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') as npc_names
            FROM npc_clusters 
            WHERE area_level <= 10 OR area_level IS NULL
            GROUP BY area_name, area_level
            ORDER BY area_level, area_name
        `);

    console.log('\n🌱 新手地区详情 (10级以下):');
    console.table(newbieAreas.map(row => ({
      '地区名称': row.area_name,
      '地区等级': row.area_level || '未知',
      'NPC数量': row.npc_count,
      '平均攻击力': Math.round(row.avg_attack || 0),
      'NPC名称': row.npc_names.length > 50 ? row.npc_names.substring(0, 50) + '...' : row.npc_names
    })));

    // 攻击力分布
    const [attackStats] = await conn.execute(`
            SELECT 
                CASE 
                    WHEN attack_power = 0 THEN '无攻击力'
                    WHEN attack_power <= 20 THEN '1-20'
                    WHEN attack_power <= 50 THEN '21-50'
                    WHEN attack_power <= 100 THEN '51-100'
                    ELSE '100以上'
                END as attack_range,
                COUNT(*) as count
            FROM npc_clusters 
            GROUP BY 
                CASE 
                    WHEN attack_power = 0 THEN '无攻击力'
                    WHEN attack_power <= 20 THEN '1-20'
                    WHEN attack_power <= 50 THEN '21-50'
                    WHEN attack_power <= 100 THEN '51-100'
                    ELSE '100以上'
                END
            ORDER BY MIN(attack_power)
        `);

    console.log('\n⚔️ 攻击力分布:');
    console.table(attackStats.map(row => ({
      '攻击力范围': row.attack_range,
      'NPC数量': row.count
    })));

  } finally {
    await conn.end();
  }
}

if (require.main === module) {
  showNpcStats().catch((err) => {
    console.error('查询失败:', err.message);
    process.exit(1);
  });
}

module.exports = { showNpcStats };

